/**
 * Created by xiaolinzhang on 2016/11/1.
 */


var logger = require('../libs/log').logger;
var knex = require('./sqlDao');

exports.getProd = function (data,cb) {
    var subquery = knex().from('in_prod_main').max('prod_id').groupBy('prod_name').where('state','1');
    knex().column(['in_prod_main.prod_id','in_prod_main.prod_name','in_prod_main.prod_desc','in_protocal_main.protocal_version','in_protocal_main.protocal_url','in_channel_prod_rel.price','in_prod_main.prod_type'])
        .select().from('in_prod_main').innerJoin('in_protocal_main','in_prod_main.protocal_id','=','in_protocal_main.protocal_id')
        .innerJoin('in_channel_prod_rel','in_prod_main.prod_id','=','in_channel_prod_rel.prod_id')
        .andWhere('in_channel_prod_rel.status',1).andWhere('in_channel_prod_rel.channel_id',data.org_id).andWhere('in_prod_main.prod_id','>',10000)
        .whereIn('in_prod_main.prod_id',subquery).then(function (res) {
        logger.debug(res);
        cb(null,JSON.stringify(res));
    }).catch(function (err) {
        logger.error(err);
        cb(err,null);
    });
}

exports.getAnn = function (data,cb) {
    knex().column(['notice_text']).select().from('in_notice').where('status',1).orderBy('create_time','desc').limit(3)
        .then(function (res) {
            logger.debug(res);
            cb(null,JSON.stringify(res));
        }).catch(function (err) {
        logger.error(err);
        cb(err,null);
    });
}

exports.getOutTradeNo = function (data,cb) {
    knex().column(['in_channel_prod_rel.*','in_cardmgmt.card_id'])
        .select().from('in_channel_prod_rel').innerJoin('in_cardmgmt','in_channel_prod_rel.prod_id','=','in_cardmgmt.prod_id')
        .where('in_cardmgmt.org_id',data.org_id).andWhere('in_cardmgmt.out_trade_no',data.out_trade_no)
        .then(function (res) {
            logger.debug(res);
            cb(null,JSON.stringify(res));
        }).catch(function (err) {
        logger.error(err);
        cb(err,null);
    });
}

exports.getUserInfo = function (data,cb) {
    knex().column(['c.emp_id', 'a.ops_id', 'a.name','b.pid','b.channel_id','d.pid as org_id','a.password' ,'a.state','a.fail', 'b.name as channel_name','c.state as cstate','c.realname','b.pay_method'])
        .select().from('in_user_ops as a').innerJoin('in_channelmgmt as b','a.channel_id','=','b.channel_id')
        .innerJoin('in_ops_openid as c','a.ops_id','=','c.ops_id')
        .innerJoin('in_channelmgmt as d','b.pid','=','d.channel_id')
        .where('c.open_id',data.open_id).orderBy('c.last_login_time','desc').then(function (res) {
        logger.debug(res);
        cb(null,JSON.stringify(res))
    }).catch(function (err) {
        logger.error(err);
        cb(err,null);
    })
}


exports.getCardRecord = function (data,sdate,edate,cb) {
    knex().column(['a.card_id','a.secret_string','a.cellno','a.out_trade_no','a.createdate','a.invalidate','a.verifydone', 'b.pay_type','c.insure_id','d.bodyname','e.prod_type','c.state as cstate'])
        .select().from('in_cardmgmt as a').leftJoin('in_pay_detail as b','a.card_id','b.card_id')
        .leftJoin('in_insureorder_main as c','a.card_id','c.card_id')
        .leftJoin('in_user_busi as d',function () {
            this.on('c.cellno','=','d.cellno').andOn('c.udid','=','d.udid')
        })
        .leftJoin('in_prod_main as e','c.prod_id','e.prod_id')
        .where(data).andWhere('a.createdate','>=',sdate).andWhere('a.createdate','<',edate).orderBy('a.createdate','desc').then(function (res) {
        logger.debug(JSON.stringify(res));
        cb(null,JSON.stringify(res))
    }).catch(function (err) {
        logger.error(err);
        cb(err,null);
    })
}

exports.getEmpOrdSum = function (data,cb) {
    knex().count('a.order_no as cc').sum('d.commision_value as fee')
        .select().from('in_pay_detail as a')
        .innerJoin('in_insureorder_main as c','a.pay_order_no', '=', 'c.out_trade_no')
        .innerJoin('in_channel_prod_rel as d','c.channel_prod_id','=','d.id')
        .where('a.PAY_RESULT','=',2).andWhere('c.state','=',1).andWhere('a.EMP_ID' ,'=',data.emp_id).whereRaw('YEARWEEK(date_format(a.create_time,\'%Y-%m-%d\')) = YEARWEEK(now())')
        .then(function (res) {
            logger.debug(JSON.stringify(res));
            cb(null,JSON.stringify(res))
        }).catch(function (err) {
        logger.error(err);
        cb(err,null);
    })
}

exports.getEmpRank = function (data,cb) {
    // knex().with('A',knex.raw('SELECT count(*) as cc,a.emp_id,ifnull(b.realname,b.nickname) as rname,'+
    //     'b.avatar,d.name as cname FROM in_pay_detail a,in_ops_openid b,in_user_ops c,in_channelmgmt d,'+
    //     'in_insureorder_main e where a.pay_order_no = e.out_trade_no and e.state=1 and a.emp_id = b.emp_id'+ 
    //     'and b.ops_id=c.ops_id and c.channel_id=d.channel_id and a.EMP_ID IS NOT NULL AND a.PAY_RESULT=2 '+
    //     'AND a.channel_id=:channel_id AND YEARWEEK(date_format(a.create_time,\'%Y-%m-%d\')) = YEARWEEK(now()) GROUP BY a.EMP_ID order by 1 desc,a.emp_id asc',{
    //             channel_id:data.channel_id
    //     })).select(knex.raw('A.*,@rank:=@rank+1 as pm'))
    // .from(knex.raw('A ,(SELECT @rank:=0) B')).then(function(res){
    //     logger.debug(JSON.stringify(res));
    //     cb(null,JSON.stringify(res))
    // }).catch(function (err) {
    //     logger.error(err);
    //     cb(err,null);
    // })
    knex().raw('SELECT * FROM (SELECT A.*,@rank:=@rank+1 as pm FROM'+
        ' (SELECT count(*) as cc,emp_id FROM in_pay_detail a,in_insureorder_main c WHERE a.pay_order_no = c.out_trade_no '+
        'and c.state=1 and a.EMP_ID IS NOT NULL AND a.PAY_RESULT=2 AND a.channel_id=:channel_id AND YEARWEEK(date_format(a.create_time,\'%Y-%m-%d\')) = YEARWEEK(now()) '+
        'GROUP BY EMP_ID order by 1 desc,a.emp_id asc) A ,(SELECT @rank:=0) B) M'+
        ' WHERE M.emp_id = :emp_id ORDER BY M.PM',{
            channel_id:data.channel_id,
            emp_id:data.emp_id
        }).then(function(res){
        logger.debug(res);
        cb(null,res);
    }).catch(function(err){
        logger.error(err);
        cb(err,null);
    })
}